/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.nts.pointsstore;

/**
 *
 * @author himagirinathan.t
 */

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import java.util.ArrayList;
import java.util.List;

public class DataHelper
{

   private static final String DATABASE_NAME = "pointsstore.db";
   private static final int DATABASE_VERSION = 1;
   private static final String USERS_TABLE = "users";
   private static final String PRODUCTS_TABLE = "products";
   private static final String CARTLIST_TABLE = "cartlist";
   private Context context;
   private SQLiteDatabase db;
   private SQLiteStatement insertStmt;
   private static final String INSERT_USERS = "insert into " + USERS_TABLE + "(usrname,pwd,emailid,status,userpoints) values (?,?,?,?,?)";
   private static final String INSERT_PRODUCTS = "insert into " + PRODUCTS_TABLE + "(productname,productpoints,mrp) values (?,?,?)";
   //private static final String INSERT_CARLIST = "insert into " + CARTLIST_TABLE + "(productname,productpoints,mrp) values (?,?,?)";

   public DataHelper(Context context)
   {
      this.context = context;
      OpenHelper openHelper = new OpenHelper(this.context);
      this.db = openHelper.getWritableDatabase();
   }

   public long insertUser(String name,String pwd,String email,String status,int userpoints)
   {
     long l;
     this.insertStmt = this.db.compileStatement(INSERT_USERS);
     this.insertStmt.bindString(1, name);
     this.insertStmt.bindString(2, pwd);
     this.insertStmt.bindString(3, email);
     this.insertStmt.bindString(4, status);
     this.insertStmt.bindLong(5, userpoints);
     l=this.insertStmt.executeInsert();
     this.insertStmt.close();
     return  l;
   }

   public void deleteAllUsers()
   {
      this.db.delete(USERS_TABLE, null, null);
   }

   public int countUsers()
   {
       Cursor cursor = this.db.query(USERS_TABLE, new String[] { "usrid" }, null, null, null, null, null);
       int count=cursor.getCount();
       cursor.close();
       return count;
   }

   public List<String[]> selectAllUsers()
   {
      List<String[]> list = new ArrayList<String[]>();
      Cursor cursor = this.db.query(USERS_TABLE, new String[] { "usrid","usrname","pwd","emailid","status","userpoints" }, null, null, null, null, "usrname asc");
      if (cursor.moveToFirst())
      {
         String strrow[]=new String[6];
         do
         {
           strrow[0]=cursor.getString(0);
           strrow[1]=cursor.getString(1);
           strrow[2]=cursor.getString(2);
           strrow[3]=cursor.getString(3);
           strrow[4]=cursor.getString(4);
           strrow[5]=cursor.getString(5);
           list.add(strrow);
         } while (cursor.moveToNext());
      }
      if (cursor != null && !cursor.isClosed())
      {
         cursor.close();
      }
      return list;
   }

   public String[] selectUserDetails(long id)
   {
      String row[]=new String[4];
      Cursor cursor = this.db.query(USERS_TABLE, new String[] { "usrname","emailid","status","userpoints" }, "usrid=?", new String[]{""+id}, null, null, "usrname asc");
      if(cursor.getCount()==1)
        if (cursor.moveToFirst())
        {
           row[0]=cursor.getString(0);
           row[1]=cursor.getString(1);
           row[2]=cursor.getString(2);
           row[3]=cursor.getString(3);
           if (cursor != null && !cursor.isClosed())
           {
            cursor.close();
          }
        }
      return row;
   }

   public int selectUserPoints(long id)
   {
      int ret=0;
      Cursor cursor = this.db.query(USERS_TABLE, new String[] { "userpoints" }, "usrid=?", new String[]{""+id}, null, null,null);
      if(cursor.getCount()==1)
        if (cursor.moveToFirst())
        {
         ret=cursor.getInt(0);
         if (cursor != null && !cursor.isClosed())
         {
          cursor.close();
         }
       }
      return ret;
   }

   public long selectUser(String usrname,String pwd)
   {
      long ret=-1;
      Cursor cursor = this.db.query(USERS_TABLE, new String[] { "usrid" }, "usrname=? and pwd=?", new String[]{usrname,pwd}, null, null, "usrname asc");
      if(cursor.getCount()==1)
        if (cursor.moveToFirst())
           ret=cursor.getLong(0);
      if (cursor != null && !cursor.isClosed())
      {
         cursor.close();
      }
      return ret;
   }

   public long insertProduct(String productname,int productpoints,double mrp)
   {
     long l;
     this.insertStmt = this.db.compileStatement(INSERT_PRODUCTS);
     this.insertStmt.bindString(1, productname);
     this.insertStmt.bindLong(2, productpoints);
     this.insertStmt.bindDouble(3, mrp);
     l=this.insertStmt.executeInsert();
     this.insertStmt.close();
     return  l;
   }

   public void deleteAllProducts()
   {
      this.db.delete(PRODUCTS_TABLE, null, null);
   }

   public int countProducts()
   {
       Cursor cursor = this.db.query(PRODUCTS_TABLE, new String[] { "productid" }, null, null, null, null, null);
       int count=cursor.getCount();
       cursor.close();
       return count;
   }

   public List<String[]> selectAllProducts()
   {
      List<String[]> list = new ArrayList<String[]>();
      Cursor cursor = this.db.query(PRODUCTS_TABLE, new String[] { "productid","productname","productpoints","mrp" }, null, null, null, null, "productname asc");
      if (cursor.moveToFirst())
      {
         do
         {
           String strrow[]=new String[4];
           strrow[0]=cursor.getString(0);
           strrow[1]=cursor.getString(1);
           strrow[2]=cursor.getString(2);
           strrow[3]=cursor.getString(3);
           list.add(strrow);
         } while (cursor.moveToNext());
      }
      if (cursor != null && !cursor.isClosed())
      {
         cursor.close();
      }
      return list;
   }

   class OpenHelper extends SQLiteOpenHelper
   {

      OpenHelper(Context context)
      {
         super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }

      @Override
      public void onCreate(SQLiteDatabase db)
      {
         db.execSQL("CREATE TABLE " + USERS_TABLE + "(usrid INTEGER PRIMARY KEY AUTOINCREMENT, usrname TEXT, pwd TEXT ,emailid TEXT, status TEXT, userpoints INTEGER)");
         db.execSQL("CREATE TABLE " + PRODUCTS_TABLE + "(productid INTEGER PRIMARY KEY AUTOINCREMENT, productname TEXT, productpoints INTEGER , mrp REAL)");
         db.execSQL("CREATE TABLE " + CARTLIST_TABLE + "(cartid INTEGER , userid INTEGER , productid INTEGER , status TEXT dateandtime TEXT)");
      }

      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
      {
         db.execSQL("DROP TABLE IF EXISTS " + USERS_TABLE);
         db.execSQL("DROP TABLE IF EXISTS " + PRODUCTS_TABLE);
         db.execSQL("DROP TABLE IF EXISTS " + CARTLIST_TABLE);
         onCreate(db);
      }
   }
}

